using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;

using System.Data.SqlClient;

using System.IO;

namespace G2007.CodeGen.Library.DBConvertor
{
	public class DBToScript
	{

		public const string FIELD_CLASS_NAME_MATCH_EXTENDER = "__value";


		public static bool CreateFile ( string outputFile, string connectionString , string name , string prefix , string outputFolder )
		{
			using ( StreamWriter sw = File.CreateText ( outputFile ) )
			{
			
				sw.Write ( GetScriptForDB ( connectionString , name , prefix , outputFolder ) );
				
				sw.Close () ;
						
			}
			
			return true;
		}
	
	
		static string GetScriptForDB ( string connectionString , string name , string prefix , string outputFolder )
		{
		
			StringBuilder sb = new StringBuilder();
			sb.Append ( "project\t" + name + "\n" );
			sb.Append ( "database\t" + connectionString + "\n" );
			sb.Append ( "prefix\t" + prefix + "\n" );
			sb.Append ( "output\t" + outputFolder + "\n\n\n\n\n" );
			
			
			
			using ( SqlConnection sqlCon = new SqlConnection ( connectionString ) )
			{
				sqlCon.Open ();

				List<string> tableNames = new List<string> ();
				
				using ( SqlCommand sqlCom = new SqlCommand ( "select [name] from sysobjects where type='u'" , sqlCon ) )
				{
					using ( SqlDataReader sqlDr= sqlCom.ExecuteReader () )
					{
					
						while ( sqlDr.Read () )
						{
							tableNames.Add ( sqlDr.GetString ( 0 ) );
						}
						
						sqlDr.Close();
					}
					
				}


				Hashtable htKeys = new Hashtable ();

				using ( SqlCommand sqlComKey = new SqlCommand ( "select kcu.TABLE_NAME, kcu.COLUMN_NAME " +
					"from INFORMATION_SCHEMA.table_constraints tc inner join INFORMATION_SCHEMA.key_column_usage kcu " +
					"ON  tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA and tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME " +
					"where tc.TABLE_SCHEMA = 'dbo' and tc.CONSTRAINT_TYPE = 'PRIMARY KEY'", sqlCon ) )
				{
					using ( SqlDataReader sqlDr = sqlComKey.ExecuteReader () )
					{
						// tableName + Column
						while ( sqlDr.Read () )
						{
							if ( !  htKeys.ContainsKey ( sqlDr.GetString ( 0 ) + sqlDr.GetString ( 1 ) ) )
							{
								htKeys.Add ( sqlDr.GetString ( 0 ) + sqlDr.GetString ( 1 ), sqlDr.GetString ( 1 ) );
							}
						}

					}
					
				}


				Hashtable htIdentity = new Hashtable ();


				using ( SqlCommand sqlComIdent = new SqlCommand ( "SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1", sqlCon ) )
				{
					using ( SqlDataReader sqlDr = sqlComIdent.ExecuteReader () )
					{
						// tableName + Column
						while ( sqlDr.Read () )
						{
							if ( !htIdentity.ContainsKey ( sqlDr.GetString ( 0 ) + sqlDr.GetString ( 1 ) ) )
							{
								htIdentity.Add ( sqlDr.GetString ( 0 ) + sqlDr.GetString ( 1 ), sqlDr.GetString ( 1 ) );
							}
						}

					}

				}


				
				
				foreach ( string tableName in tableNames )
				{
					sb.Append ( "class\t" + tableName + "\n" );
					
					using ( SqlCommand sqlCom = new SqlCommand ( "select column_name, data_type from information_schema.columns where table_name='" +
						tableName + "' order by ordinal_position" , sqlCon ) )
					{
						
						using ( SqlDataReader sqlDr = sqlCom.ExecuteReader () )
						{
						
							while ( sqlDr.Read () )
							{
								string type = sqlDr.GetString ( 1 );
								string fieldName = sqlDr.GetString ( 0 );

								if ( fieldName.ToUpper ().Equals ( tableName.ToUpper () ) )
								{
									fieldName += FIELD_CLASS_NAME_MATCH_EXTENDER;
								}
							
								switch ( type )
								{
									case "varchar":
									case "nvarchar":
									case "nchar":
									case "char":
										type = "string";
										break;
									case "numeric":
									case "money":
									case "float":
									case "decimal":
										type = "double";
										break;
									case "datetime":
										type= "DateTime";
										break;
									case "text":
									case "ntext":
										type = "bigstring";
										break;
									case "int":
									case "tinyint":
									case "smallint":
									case "image":
										type="int";
										break;
									case "uniqueidentifier":
										type="Guid";
										break;
									case "bit":
										type="bool";
										break;
									default:
										// problem here possibly.
										type = type;
										break;
									
								}

								// Have a look if the field is a primary key, if it is then we can make it as such in the
								// configuration file.
								bool isKey = htKeys.ContainsKey ( tableName + fieldName );

								bool isIdent = htIdentity.ContainsKey ( tableName + fieldName );

		
								if ( type != string.Empty )
								{
									sb.Append ( fieldName + "\t" + type + (isIdent ? "\tidentity" : (( isKey ? "\tkey" : "" ) )) + "\n" );
								}
								
							}
						
						}
					
					}
					
					sb.Append ( "end class\n\n\n");
				}			
			
				sqlCon.Close ();
			}
			
		
			return sb.ToString ();
		
		}
		
		
	
	}
}
